# -*- coding:utf-8 -*-
from django.db.utils import IntegrityError

import requests
from openpyxl import load_workbook
from bs4 import BeautifulSoup
import pymysql
import json
from pathlib import Path
from . import views


def get_mean(val):
    url = 'http://dict.youdao.com/jsonapi?jsonversion=2&client=mobile&q=%s&network=wifi' % val
    re = requests.get(url)
    js = re.json()
    means = js['video_sents']['word_info']['sense']
    s = ''
    for i in means:
        s += i
    return s


def get_mean2(val):
    url = 'http://apii.dict.cn/mini.php?q=%s' % val
    re = requests.get(url)
    soup = BeautifulSoup(re.text, features="html.parser")
    p_node = soup.find('div', id='e')
    if p_node is not None:
        return p_node.text
    return ''


def do_exl_change(file):
    wb = load_workbook(file)
    sheet = wb[wb.sheetnames[0]]
    for i in range(1, sheet.max_row + 1):
        index1 = 'A' + str(i)
        index2 = 'B' + str(i)
        val = sheet[index1].value
        # if val != '' :
        #     continue
        try:
            sheet[index2] = get_mean2(val)
        except KeyError:
            pass
    wb.save(file)


index = 0


def write_next():
    global index
    index += 1
    return str(index)


host = 'www.honosayaka.xyz'
user = 'tank'
password = '123456'
database = 'english'


def get_vocabulary(num):
    conn = pymysql.connect(host=host, user=user, password=password, database=database)
    cursor = conn.cursor()
    sqls = '''SELECT
    *
FROM
    vocabulary_copy AS t1
JOIN (
    SELECT
        ROUND(
            RAND() * (
                (SELECT MAX(id) FROM `vocabulary_copy`) - (SELECT MIN(id) FROM `vocabulary_copy`)
            ) + (SELECT MIN(id) FROM `vocabulary_copy`)
        ) AS id
) AS t2
WHERE
    t1.id >= t2.id
ORDER BY
    t1.id
LIMIT %d;''' % num
    cursor.execute(sqls)
    r = cursor.fetchall()
    cursor.close()
    conn.close()
    data = []
    for i in r:
        data.append({'voca': i[1], 'mean': i[2]})
    return json.dumps(data)


def my_insert(file):
    try:
        conn = pymysql.connect(host=host, user=user, password=password, database=database)
        cursor = conn.cursor()
        wb = load_workbook(file)
        sheet = wb['Sheet1']
        for i in range(1, sheet.max_row + 1):
            index1 = 'A' + str(i)
            index2 = 'B' + str(i)
            index3 = 'C' + str(i)
            val1 = sheet[index1].value
            val2 = sheet[index2].value
            val3 = sheet[index3].value
            sql = "insert into vocabulary_copy(voca,mean,unit) values('%s','%s',%s) " % (val1,val2,val3)
            print(sql)
            cursor.execute(sql)

          # 执行插入数据

        conn.commit()
        cursor.close()
        conn.close()
        print('insert ok')
    except Exception as e:
        print(e)


def read_exl_insert(file):
    state = 1
    from .models import Tango
    wb = load_workbook(file)
    sheet = wb['Sheet1']
    for i in range(1, sheet.max_row + 1):
        index1 = 'A' + str(i)
        index2 = 'B' + str(i)
        index3 = 'C' + str(i)
        try:
            Tango(tango=sheet[index1].value, mean=sheet[index2].value, unit=sheet[index3].value).save()
        except IntegrityError:
            state = 0
    if state == 0:
        raise IntegrityError
    else:
        return
        # if val != '' :
        #     continue


def get_file_list():
    ls = []
    dir = views.BASE_DIR / 'uploads'
    for i in dir.iterdir():
        ls.append(str(i.name))
    return ls
